package it.davial.mgwt.server;



public class Sql {

	
	public class Patient{
		public static final String SELECT_ALL = "SELECT ID_PATIENT,NAME,SURNAME,DATE_FORMAT(STR_TO_DATE(DATA_BIRTH, '%Y%m%d/'),'%d/%m/%Y') DATA_BIRTH,ADDRESS_STREET," +
												        "ADDRESS_NUMBER,ADDRESS_CODE,ADDRESS_PROVINCE,ADDRESS_CITY," +
												        "PHONE,CELLULAR,CODE_FUNCTION,DATE_FORMAT(STR_TO_DATE(DATA_INS, '%Y%m%d/'),'%d/%m/%Y') DATA_INS,CODE_USER_INS," +
												        "DATE_FORMAT(STR_TO_DATE(DATA_UPD, '%Y%m%d/'),'%d/%m/%Y') DATA_UPD," +
												        "CODE_USER_UPD,STATE FROM PATIENT WHERE STATE=0";

		public static final String SELECT_BY_ID = "SELECT ID_PATIENT,NAME,SURNAME,DATA_BIRTH,ADDRESS_STREET," +
				                                          "ADDRESS_NUMBER,ADDRESS_CODE,ADDRESS_PROVINCE,ADDRESS_CITY," +
				                                          "PHONE,CELLULAR,CODE_FUNCTION,DATE_FORMAT(STR_TO_DATE(DATA_INS, '%Y%m%d/'),'%d/%m/%Y') DATA_INS," +
				                                          "CODE_USER_INS,DATE_FORMAT(STR_TO_DATE(DATA_UPD, '%Y%m%d/'),'%d/%m/%Y') DATA_UPD," +
				                                          "CODE_USER_UPD,STATE FROM PATIENT WHERE ID_PATIENT=? AND STATE=0";
		
		public static final String SELECT_BY_SURNAME_DATE ="SELECT ID_PATIENT,NAME,SURNAME,DATA_BIRTH,ADDRESS_STREET,"+
        														  "ADDRESS_NUMBER,ADDRESS_CODE,ADDRESS_PROVINCE,ADDRESS_CITY,"+
        														  "PHONE,CELLULAR,CODE_FUNCTION,DATE_FORMAT(STR_TO_DATE(DATA_INS, '%Y%m%d/'),'%d/%m/%Y') DATA_INS," +
        														  "CODE_USER_INS,DATE_FORMAT(STR_TO_DATE(DATA_UPD, '%Y%m%d/'),'%d/%m/%Y') DATA_UPD,"+ 
        														  "CODE_USER_UPD,STATE FROM PATIENT WHERE   (SURNAME =? OR ? IS NULL) "+
        														  "AND  (DATA_INS=? OR ? IS NULL) AND STATE=0";
	    
	
	}
	public class Visit{
		public static final String SELECT_ALL = "SELECT ID_VISIT,ID_PATIENT,DESC_VISIT,DATA_VISIT,NOTE_VISIT," +
													  "DIAGNOSIS_VISIT,CODE_FUNCTION,DATE_FORMAT(STR_TO_DATE(DATA_INS, '%Y%m%d/'),'%d/%m/%Y') DATA_INS," +
													  "CODE_USER_INS,DATE_FORMAT(STR_TO_DATE(DATA_UPD, '%Y%m%d/'),'%d/%m/%Y') DATA_UPD," +
													  "CODE_USER_UPD,STATE FROM VISIT WHERE STATE=0";

		public static final String SELECT_BY_ID = "SELECT ID_VISIT,ID_PATIENT,DESC_VISIT,DATA_VISIT,NOTE_VISIT," +
														"DIAGNOSIS_VISIT,CODE_FUNCTION,DATE_FORMAT(STR_TO_DATE(DATA_INS, '%Y%m%d/'),'%d/%m/%Y') DATA_INS," +
														"CODE_USER_INS,DATE_FORMAT(STR_TO_DATE(DATA_UPD, '%Y%m%d/'),'%d/%m/%Y') DATA_UPD," +
														"CODE_USER_UPD,STATE FROM VISIT WHERE ID_VISIT=? AND STATE=0";

	
		 public static final String SELECT_BY_IDPERSON_DATE ="SELECT V.ID_VISIT,V.ID_PATIENT,V.DESC_VISIT,V.DATA_VISIT,V.NOTE_VISIT," +
																   "V.DIAGNOSIS_VISIT,V.CODE_FUNCTION,DATE_FORMAT(STR_TO_DATE(V.DATA_INS, '%Y%m%d/'),'%d/%m/%Y') DATA_INS," +
																   "V.CODE_USER_INS,DATE_FORMAT(STR_TO_DATE(V.DATA_UPD, '%Y%m%d/'),'%d/%m/%Y') DATA_UPD," +
																   "V.CODE_USER_UPD,V.STATE FROM VISIT V INNER JOIN PATIENT ON " +
			 													   "V.ID_PATIENT=PATIENT.ID_PATIENT " +
			 													   "WHERE (PATIENT.ID_PATIENT=? OR ? IS NULL) " +
		 														   "AND  (V.DATA_INS=? OR ? IS NULL) AND V.STATE=0"; 
	}

	public class Area{
		public static final String SELECT_ALL = "SELECT ID_AREA,ID_FIGURE,POLYGON,CODE_FUNCTION,DATE_FORMAT(STR_TO_DATE(DATA_INS, '%Y%m%d/'),'%d/%m/%Y') DATA_INS,CODE_USER_INS," +
													 "DATE_FORMAT(STR_TO_DATE(DATA_UPD, '%Y%m%d/'),'%d/%m/%Y') DATA_UPD,CODE_USER_UPD,STATE FROM AREA WHERE STATE=0";

		public static final String SELECT_BY_ID =  "SELECT ID_AREA,ID_FIGURE,POLYGON,CODE_FUNCTION,DATE_FORMAT(STR_TO_DATE(DATA_INS, '%Y%m%d/'),'%d/%m/%Y') DATA_INS,CODE_USER_INS," +
		 												"DATE_FORMAT(STR_TO_DATE(DATA_UPD, '%Y%m%d/'),'%d/%m/%Y') DATA_UPD,CODE_USER_UPD,STATE FROM AREA WHERE ID_AREA=? AND STATE=0";

	}

	
	  
	public class Photo{
		public static final String SELECT_ALL = "SELECT ID_PHOTO,ID_VISIT,ID_AREA,DESC_PHOTO,NOTE_PHOTO,PHOTO,DATE_FORMAT(STR_TO_DATE(DATA_INS, '%Y%m%d/'),'%d/%m/%Y') DATA_INS," +
													   "CODE_USER_INS,DATE_FORMAT(STR_TO_DATE(DATA_UPD, '%Y%m%d/'),'%d/%m/%Y') DATA_UPD,CODE_USER_UPD,STATE FROM PHOTO WHERE STATE=0";
		
		public static final String SELECT_BY_ID = "SELECT ID_PHOTO,ID_VISIT,ID_AREA,DESC_PHOTO,NOTE_PHOTO,PHOTO,DATE_FORMAT(STR_TO_DATE(DATA_INS, '%Y%m%d/'),'%d/%m/%Y') DATA_INS," +
		   												"CODE_USER_INS,DATE_FORMAT(STR_TO_DATE(DATA_UPD, '%Y%m%d/'),'%d/%m/%Y') DATA_UPD,CODE_USER_UPD,STATE FROM PHOTO WHERE ID_PHOTO =? AND STATE=0";
	
	    public static final String SELECT_BY_IDPERSON_DATE ="SELECT P.ID_PHOTO,P.ID_VISIT,P.ID_AREA,P.DESC_PHOTO,P.NOTE_PHOTO,P.PHOTO," +
	    														  "DATE_FORMAT(STR_TO_DATE(P.DATA_INS, '%Y%m%d/'),'%d/%m/%Y') DATA_INS," +
													   			  "P.CODE_USER_INS,DATE_FORMAT(STR_TO_DATE(P.DATA_UPD, '%Y%m%d/'),'%d/%m/%Y') DATA_UPD,P.CODE_USER_UPD," +
													   			  "P.STATE FROM PHOTO P INNER JOIN " +
	    														  "VISIT ON VISIT.ID_VISIT= P.ID_VISIT INNER JOIN PATIENT ON " +
	    														  "VISIT.ID_PATIENT=PATIENT.ID_PATIENT WHERE (PATIENT.ID_PATIENT=? OR ? IS NULL) " +
	    														  "AND  (P.DATA_INS=? OR ? IS NULL) AND STATE=0"; 
	}

	public class Figure{
		public static final String SELECT_ALL = "SELECT  ID_FIGURE,DESC_FIGURE,NAME_FILE,CODE_FUNCTION," +
													   "DATE_FORMAT(STR_TO_DATE(DATA_INS, '%Y%m%d/'),'%d/%m/%Y') DATA_INS," +
													   "CODE_USER_INS,DATE_FORMAT(STR_TO_DATE(DATA_UPD, '%Y%m%d/'),'%d/%m/%Y') DATA_UPD,CODE_USER_UPD,STATE FROM FIGURE" +
													   " WHERE STATE=0";
		
		public static final String SELECT_BY_ID = "SELECT  ID_FIGURE,DESC_FIGURE,NAME_FILE,CODE_FUNCTION," +
		   												"DATE_FORMAT(STR_TO_DATE(DATA_INS, '%Y%m%d/'),'%d/%m/%Y') DATA_INS," +
		   												"CODE_USER_INS,DATE_FORMAT(STR_TO_DATE(DATA_UPD, '%Y%m%d/'),'%d/%m/%Y') DATA_UPD,CODE_USER_UPD,STATE FROM FIGURE" +
		   												 " WHERE ID_FIGURE=? AND STATE=0";
		
		public static final String SELECT_AREA = "SELECT A.ID_AREA,A.ID_FIGURE,A.POLYGON ,A.CODE_FUNCTION," +
												 "DATE_FORMAT(STR_TO_DATE(A.DATA_INS, '%Y%m%d/'),'%d/%m/%Y') DATA_INS,A.CODE_USER_INS," +
												 "DATE_FORMAT(STR_TO_DATE(A.DATA_UPD, '%Y%m%d/'),'%d/%m/%Y') DATA_UPD,A.CODE_USER_UPD," +
												 "A.STATE,A.ID_FIGURE_CHILD FROM AREA A INNER JOIN FIGURE ON FIGURE.ID_FIGURE= " +
												 "A.ID_FIGURE WHERE FIGURE.ID_FIGURE=?";
	}
    public class Utility{
    	public static final String SELECT_ID="SELECT ID,ID_FIGURE,ID_AREA,ID_PHOTO,ID_VISIT,ID_PATIENT FROM MANAGERID";
	}
	
	
}
